package com.chuangke.dev.service.impl;

import java.io.IOException;
import java.io.StringWriter;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.velocity.Template;
import org.apache.velocity.VelocityContext;
import org.apache.velocity.app.Velocity;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import com.alibaba.fastjson2.JSON;
import com.alibaba.fastjson2.JSONObject;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.baomidou.mybatisplus.extension.toolkit.SqlRunner;
import com.chuangke.common.db.page.CkPageQuery;
import com.chuangke.common.db.page.PageUtil;
import com.chuangke.common.exception.ChuangkeException;
import com.chuangke.common.http.HttpResult;
import com.chuangke.common.utils.SecurityUtils;
import com.chuangke.dev.bean.ImportDataRequest;
import com.chuangke.dev.constant.GenConstants;
import com.chuangke.dev.dao.SysModelTableMapper;
import com.chuangke.dev.model.SysModelColumn;
import com.chuangke.dev.model.SysModelTable;
import com.chuangke.dev.service.SysModelColumnService;
import com.chuangke.dev.service.SysModelTableService;
import com.chuangke.dev.util.GenUtils;
import com.chuangke.dev.util.VelocityInitializer;
import com.chuangke.dev.util.VelocityUtils;
import com.chuangke.dev.util.WorkbookUtils;

import cn.hutool.core.util.StrUtil;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.StringValue;
//import net.sf.jsqlparser.expression.operators.relational.MultiExpressionList;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.insert.Insert;

/**
 * 业务 服务层实现
 * 
 * @author chaungke
 */
@Service
public class SysModelTableServiceImpl extends ServiceImpl<SysModelTableMapper, SysModelTable>
		implements SysModelTableService {

	private static final String CREATED_TABLE="1";
	private static final Logger log = LoggerFactory.getLogger(SysModelTableServiceImpl.class);

	@Autowired
	private SysModelTableMapper genTableMapper;

	@Autowired
	private SysModelColumnService genTableColumnService;

	/**
	 * 查询业务列表
	 * 
	 * @param genTable 业务信息
	 * @return 业务集合
	 */
	@Override
	public List<SysModelTable> listModels(SysModelTable genTable) {
		QueryWrapper<SysModelTable> queryWrapper = new QueryWrapper<>();

		String tableName = genTable.getTableName();
		if (StrUtil.isNotBlank(tableName)) {
			queryWrapper.lambda().like(SysModelTable::getTableName, tableName);
		}

		String tableComment = genTable.getTableComment();
		if (StrUtil.isNotBlank(tableComment)) {
			queryWrapper.lambda().like(SysModelTable::getTableComment, tableComment);
		}
		
		String type=genTable.getType();
		if (StrUtil.isNotBlank(type)) {
			queryWrapper.lambda().like(SysModelTable::getType, type);
		}

		return genTableMapper.selectList(queryWrapper);
	}

	/**
	 * 修改业务
	 * 
	 * @param genTable 业务信息
	 * @return 结果
	 */
	@Override
	@Transactional
	public void updateModel(SysModelTable genTable) {
		genTable.setOptions(JSON.toJSONString(genTable.getParams()));
		genTableMapper.updateById(genTable);
		genTableColumnService.updateBatchById(genTable.getColumns());
	}

	/**
	 * 删除业务对象
	 * 
	 * @param tableIds 需要删除的数据ID
	 * @return 结果
	 */
	@Override
	@Transactional
	public void deleteModelByIds(List<String> tableIds) {
		genTableMapper.deleteByIds(tableIds);
		genTableColumnService.remove(
				new QueryWrapper<SysModelColumn>().lambda().in(SysModelColumn::getTableId, tableIds));
	}

	/**
	 * 导入表结构
	 * 
	 * @param tableList 导入表列表
	 */
	@Override
	@Transactional
	public void importModel(List<SysModelTable> tableList) {
		String operName = SecurityUtils.getUserId();
		tableList.forEach(table -> {
			GenUtils.initTable(table, operName);
			genTableMapper.insert(table);

			List<SysModelColumn> genTableColumns = genTableColumnService
					.listColumnsFromSchemaByTableName(table.getTableName());
			genTableColumns.forEach(column -> GenUtils.initColumnField(column, table));
			genTableColumnService.saveBatch(genTableColumns);
		});
	}

	public SysModelTable loadModelById(String tableId) {
		SysModelTable table = genTableMapper.selectById(tableId);
		List<SysModelColumn> columns = genTableColumnService
				.list(new QueryWrapper<SysModelColumn>().lambda().eq(SysModelColumn::getTableId, table.getTableId()));
		table.setColumns(columns);
		return table;
	}

	/**
	 * 预览代码
	 * 
	 * @param tableId 表编号
	 * @return 预览数据列表
	 */
	@Override
	public Map<String, String> previewCode(String tableId) {
		Map<String, String> dataMap = new LinkedHashMap<>();

		SysModelTable table = loadModelById(tableId);
		List<SysModelColumn> columns = table.getColumns();

		setPkColumn(table, columns);

		VelocityInitializer.initVelocity();
		VelocityContext context = VelocityUtils.prepareContext(table);

		List<String> templates = VelocityUtils.getTemplateList(table.getTplCategory());
		for (String template : templates) {
			// 渲染模板
			StringWriter sw = new StringWriter();
			Template tpl = Velocity.getTemplate(template, "UTF-8");
			tpl.merge(context, sw);
			dataMap.put(template, sw.toString());
		}
		return dataMap;
	}

	/**
	 * 批量生成代码
	 * 
	 * @return 数据
	 */
	@Override
	public Map<String, byte[]> generatorCode(String[] tableIds) {
		Map<String, byte[]> fileMap = new HashMap<>() ;
		for (String tableId : tableIds) {
			fileMap.putAll(generatorCode(tableId));
		}
		return fileMap ;
	}
	
	/**
	 * 生成代码
	 * 
	 * @return 数据
	 */
	@Override
	public Map<String,byte[]> generatorCode(String tableId) {
		Map<String, byte[]> fileMap = new HashMap<>() ;
		
		SysModelTable table = loadModelById(tableId);
		List<SysModelColumn> columns = table.getColumns();
		setPkColumn(table, columns);

		VelocityInitializer.initVelocity();
		VelocityContext context = VelocityUtils.prepareContext(table);

		List<String> templates = VelocityUtils.getTemplateList(table.getTplCategory());
		for (String template : templates) {
			try (StringWriter sw = new StringWriter()) {
				Template tpl = Velocity.getTemplate(template, "UTF-8");
				tpl.merge(context, sw);
				String fileName = VelocityUtils.getFileName(template, table) ;

				fileMap.put(fileName, sw.toString().getBytes()) ;
			} catch (IOException e) {
				log.error("渲染模板失败，表名：" + table.getTableName(), e);
			}
		}
		
		return fileMap ;
	}

	/**
	 * 修改保存参数校验
	 * 
	 * @param genTable 业务信息
	 */
	@Override
	public void validateEdit(SysModelTable genTable) {
		if (GenConstants.TPL_TREE.equals(genTable.getTplCategory())) {
			String options = JSON.toJSONString(genTable.getParams());
			JSONObject paramsObj = JSONObject.parseObject(options);
			if (StrUtil.isBlank(paramsObj.getString(GenConstants.TREE_CODE))) {
				throw new ChuangkeException("树编码字段不能为空");
			} else if (StrUtil.isBlank(paramsObj.getString(GenConstants.TREE_PARENT_CODE))) {
				throw new ChuangkeException("树父编码字段不能为空");
			} else if (StrUtil.isBlank(paramsObj.getString(GenConstants.TREE_NAME))) {
				throw new ChuangkeException("树名称字段不能为空");
			}
		}
	}

	/**
	 * 设置主键列信息
	 * 
	 * @param table   业务表信息
	 * @param columns 业务字段列表
	 */
	public void setPkColumn(SysModelTable table, List<SysModelColumn> columns) {
		for (SysModelColumn column : columns) {
			if (column.isPk()) {
				table.setPkColumn(column);
				break;
			}
		}
		if (table.getPkColumn()==null) {
			table.setPkColumn(columns.get(0));
		}
	}

	/**
	 * 设置代码生成其他选项值
	 * 
	 * @param genTable 设置后的生成对象
	 */
	public void setTableFromOptions(SysModelTable genTable) {
		JSONObject paramsObj = JSONObject.parseObject(genTable.getOptions());
		if (paramsObj != null) {
			String treeCode = paramsObj.getString(GenConstants.TREE_CODE);
			String treeParentCode = paramsObj.getString(GenConstants.TREE_PARENT_CODE);
			String treeName = paramsObj.getString(GenConstants.TREE_NAME);
			genTable.setTreeCode(treeCode);
			genTable.setTreeParentCode(treeParentCode);
			genTable.setTreeName(treeName);
		}
	}

	@Override
	public List<SysModelTable> selectTablesFromSchema(SysModelTable genTable) {
		return genTableMapper.selectTablesFromSchema(genTable);
	}

	@Override
	public List<SysModelTable> selectTablesFromSchemaByNames(String[] tableNames) {
		return genTableMapper.selectTablesFromSchemaByNames(tableNames);
	}

	@Override
	public void createTable(List<SysModelColumn> sysModelColumnList) {
		if(sysModelColumnList==null|| sysModelColumnList.isEmpty()) {
			throw new ChuangkeException("请添加要创建的列！");
		}
		String tableId=sysModelColumnList.get(0).getTableId();
		StringBuilder sql = new StringBuilder("CREATE TABLE " + tableId + "(");
        for (SysModelColumn sysModelColumn:sysModelColumnList) {
        	String columnName=sysModelColumn.getColumnName();
        	String columnType=sysModelColumn.getColumnType();
        	String columnComment=sysModelColumn.getColumnComment();
        	int columnLen=sysModelColumn.getColumnLen();
        	int decimalPoint=sysModelColumn.getDecimalPoint();
        	sql.append(" `").append(columnName).append("` ").append(columnType).append("(").append(columnLen);
        	if("decimal".equals(columnType)) {
        		sql.append(",").append(decimalPoint);
        	}
            sql.append(") ");
            sql.append(" COMMENT" + " '").append(columnComment).append("' ,");
        }
        sql = new StringBuilder(sql.substring(0, sql.length() - 1) + " ) charset=utf8; ");
        SqlRunner.db().insert(sql.toString());
		
		
	}

	@Transactional
	@Override
	public HttpResult importTableData(ImportDataRequest importDataRequest, MultipartFile file) {
		SysModelTable sysModelTable=importDataRequest.getTable();
		List<SysModelColumn> columnList=importDataRequest.getColumns();
		int startRow=sysModelTable.getStartRow();
		int endRow=sysModelTable.getEndRow();
		String tableName=sysModelTable.getTableName();
		String tableId=sysModelTable.getTableId();
		//将配置的起始行结束行存储
		genTableMapper.update(new SysModelTable(), new UpdateWrapper<SysModelTable>().lambda().set(SysModelTable::getStartRow, startRow).set(SysModelTable::getEndRow, endRow).eq(SysModelTable::getTableName, tableName));
		//将配置的导入列的名称存储
		List<String> columnIdList=columnList.stream().map(SysModelColumn::getColumnName).distinct().collect(Collectors.toList());
		List<SysModelColumn> dataList=genTableColumnService.list(new QueryWrapper<SysModelColumn>().lambda().eq(SysModelColumn::getTableId, tableId).in(SysModelColumn::getColumnName, columnIdList));
		dataList.forEach(item->{
			SysModelColumn modelColumn=columnList.stream().filter(column->column.getColumnName()!=null&&column.getColumnName().equals(item.getColumnName())).toList().get(0);
			item.setImportColumnName(modelColumn.getImportColumnName());
		});
		genTableColumnService.remove(new QueryWrapper<SysModelColumn>().lambda().eq(SysModelColumn::getTableId, tableId).in(SysModelColumn::getColumnName, columnIdList));
		genTableColumnService.saveBatch(dataList);
		
		try{
			//处理列头
			int headRow=startRow-1;
			if(headRow<0) {
				throw new ChuangkeException("请添加列头！");
			}
			Insert insert = new Insert();
	        Table table = new Table();
	        table.setName(tableName);
	        insert.setTable(table);

		    Workbook workbook = WorkbookUtils.getWorkBook(file);
		    Sheet sheetAt = workbook.getSheetAt(0);
		    List<Column> columns= new ArrayList<>();
		    List<SysModelColumn> filterColumnList= new ArrayList<>();
		    List<Integer> notExistIndexList= new ArrayList<>();
		    for(int k=0; k < sheetAt.getRow(headRow).getLastCellNum(); k++){//列名
	            Cell cell = sheetAt.getRow(headRow).getCell(k);
	            String value = WorkbookUtils.getValue(cell);
	            filterColumnList=columnList.stream().filter(item->item.getImportColumnName()!=null&&item.getImportColumnName().equals(value)).toList();
	            if(filterColumnList!=null&& !filterColumnList.isEmpty()) {
	            	String columnName=filterColumnList.get(0).getColumnName();
	            	columns.add(new Column(columnName));
	            }else {
	            	notExistIndexList.add(k);
	            }
		    }
//		    insert.setColumns(columns);
//
//			MultiExpressionList multiExpressionList = new MultiExpressionList();
//		    for (int i = startRow; i<= (Math.min(endRow, sheetAt.getLastRowNum())); i++){
//		    	List<Expression> valueList= new ArrayList<>();
//		    	for(int j=0; j < sheetAt.getRow(headRow).getLastCellNum(); j++){//循环每一行
//		    		if(notExistIndexList!=null&& notExistIndexList.contains(j)) {
//		    			continue;
//		    		}
//		            Cell cell = sheetAt.getRow(i).getCell(j);//获取每一个单元格的值
//		            String value="";
//		            if(cell!=null) {
//		            	value = WorkbookUtils.getValue(cell);//把单元格的值转成字符串
//		            }
//		            valueList.add(new StringValue(value));
//		    	}
////		    	multiExpressionList.addExpressionList(valueList);
//				insert.setSetExpressionList(valueList);
//		    }

		    SqlRunner.db().insert(insert.toString());
		}catch(Exception e){
		    throw new ChuangkeException(e.getMessage());
		}
		return null;
	}

	@Override
	public List<SysModelTable> selectAllTablesFromSchema(SysModelTable genTable) {
		return genTableMapper.selectAllTablesFromSchema(genTable);
	}

	@Override
	public IPage<Map<String,Object>> findImportData(CkPageQuery pageRequest) {
		String tableName=(String)pageRequest.get("tableName");
		String sql =  "SELECT * FROM  "+tableName;
		List<Map<String,Object>> resultList=SqlRunner.db().selectList(sql);
		return PageUtil.findPage(CkPageQuery.clone(pageRequest), resultList);
	}

	@Override
	public SysModelTable loadModelByName(String tableName) {
		SysModelTable table = genTableMapper.selectOne(new QueryWrapper<SysModelTable>().lambda().eq(SysModelTable::getTableName, tableName));
		if(table==null) {
			return new SysModelTable();
		}
		List<SysModelColumn> columns = genTableColumnService
				.list(new QueryWrapper<SysModelColumn>().lambda().eq(SysModelColumn::getTableId, table.getTableId()));
		table.setColumns(columns);
		return table;
	}

	@Transactional
	@Override
	public void createModelTable(SysModelTable sysModelTable) {
		//删除表格信息
		genTableMapper.delete(new QueryWrapper<SysModelTable>().lambda().eq(SysModelTable::getTableId, sysModelTable.getTableId()));
		genTableColumnService.remove(new QueryWrapper<SysModelColumn>().lambda().eq(SysModelColumn::getTableId, sysModelTable.getTableId()));
		//首先保存信息到sys_model_table以及sys_model_column
		String operName = SecurityUtils.getUserId();
		GenUtils.initTable(sysModelTable, operName);
		genTableMapper.insert(sysModelTable);
		List<SysModelColumn> genTableColumns = sysModelTable.getColumns();
		genTableColumns.forEach(column -> GenUtils.initColumnField(column, sysModelTable));
		genTableColumnService.saveBatch(genTableColumns);
		//根据是否创建表决定是否创建
		String createdTable=sysModelTable.getCreatedTable();
		if(CREATED_TABLE.equals(createdTable)) {
			this.createEntityTable(sysModelTable);
		}
	}
	
	private void createEntityTable(SysModelTable sysModelTable) {
		List<SysModelColumn> sysModelColumnList=sysModelTable.getColumns();
		if(sysModelColumnList==null|| sysModelColumnList.isEmpty()) {
			throw new ChuangkeException("请添加要创建的列！");
		}
		String tableName=sysModelTable.getTableName();
		StringBuilder sql = new StringBuilder("CREATE TABLE " + tableName + "(");
        for (SysModelColumn sysModelColumn:sysModelColumnList) {
        	String columnName=sysModelColumn.getColumnName();
        	String columnType=sysModelColumn.getColumnType();
        	String columnComment=sysModelColumn.getColumnComment();
        	sql.append(" `").append(columnName).append("` ").append(columnType);
            sql.append(" COMMENT" + " '").append(columnComment).append("' ,");
        }
        sql = new StringBuilder(sql.substring(0, sql.length() - 1) + " ) charset=utf8; ");
        SqlRunner.db().insert(sql.toString());
		
		
	}

}